Applying Transformations and Loading Data into Power BI

Course: Microsoft Power BI Training

Topic: Applying Transformations and Loading Data into Power BI

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Applying Transformations and Loading Data

Power BI is a business intelligence platform that helps users connect to data sources, prepare and transform data, build data models, create interactive reports, and share insights. Before data can be used effectively in a report, it often needs to be cleaned, standardized, and organized.

Applying transformations and loading data are important stages of the Power BI data preparation process. Transformations help convert raw data into a consistent structure, while loading makes the prepared data available in the Power BI semantic model for analysis and reporting.

For example, a sales dataset may contain inconsistent product names, duplicate transactions, blank values, incorrectly formatted dates, and sales amounts stored as text. Power Query can be used to correct these issues before the data is loaded into Power BI.

1.1 What Are Data Transformations?

Data transformations are operations that modify the structure, format, values, or organization of data to make it suitable for analysis. They can include removing unnecessary columns, changing data types, filtering records, splitting or merging columns, replacing values, removing duplicates, and creating calculated columns during data preparation.

1.2 What Does Loading Data Mean?

Loading data means transferring the results of a query into the Power BI semantic model or making the prepared data available to the next stage of the data workflow. In Power BI Desktop, users typically select Close & Apply in Power Query Editor to apply the transformations and load the resulting data into the model.

1.3 Example of the Complete Process

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the Power BI data preparation workflow.
• Connect to files, databases, and other supported data sources.
• Open and navigate Power Query Editor.
• Apply common data-cleaning and transformation operations.
• Change data types and manage column formats.
• Filter rows and remove unwanted records.
• Rename, remove, reorder, split, merge, and extract column values.
• Use Applied Steps to review transformation history.
• Understand the difference between loading and transforming data.
• Load prepared data into the Power BI semantic model.
• Understand query loading and connection-only queries.
• Validate data after loading and troubleshoot common errors.
• Understand refresh behavior and basic performance considerations.

3. Understanding the Power BI Data Workflow

A typical Power BI workflow begins with connecting to a data source and ends with building and sharing reports. Data transformation and loading take place between source connection and report development.

3.1 Main Stages of the Workflow

1. Connect to data: Select a supported source such as Excel, CSV, SQL Server, a web source, or another connector.
2. Preview the data: Inspect available tables, sheets, columns, and records.
3. Transform the data: Clean, filter, reshape, and standardize the source data.
4. Validate transformations: Check row counts, data types, null values, and expected results.
5. Apply and load: Apply the query changes and load the required tables into the semantic model.
6. Build the data model: Create relationships and configure fields where required.
7. Create reports: Use visuals, measures, filters, and slicers to analyze the prepared data.
8. Refresh and maintain: Update the data when source information changes.

3.2 Why Should Data Be Transformed Before Loading?

• Raw data may contain errors and inconsistent values.
• Unnecessary columns increase the amount of data being processed.
• Incorrect data types can cause calculation and sorting problems.
• Duplicate records can distort business metrics.
• Missing values can affect totals and analysis.
• Well-structured tables are easier to model and maintain.

4. Connecting to a Data Source

Before applying transformations, connect Power BI Desktop to the required data source.

4.1 Common Data Sources

4.2 Steps to Connect to an Excel File

1. Open Power BI Desktop.
2. Select Home from the ribbon.
3. Choose Get Data and select Excel workbook.
4. Browse to the required Excel file.
5. Select the file and choose Open.
6. Review the Navigator window.
7. Select the required worksheets or tables.
8. Choose Transform Data to open Power Query Editor.

Choose Transform Data when the source needs cleaning or restructuring. If the data is already suitable for analysis, loading directly may be appropriate, but the resulting data should still be validated.

5. Understanding Power Query Editor

Power Query Editor is the data preparation environment used to connect, clean, and transform data before it is loaded into the Power BI semantic model.

5.1 Main Components

5.2 Opening Power Query Editor

1. Open an existing Power BI Desktop report or create a new one.
2. Go to the Home tab.
3. Select Transform Data.
4. Wait for Power Query Editor to open.
5. Select the query that you want to transform.

6. Understanding Applied Transformations

A transformation changes the way data is represented or organized. Power Query records each transformation as a step in the query, allowing users to review, edit, or remove steps as needed.

6.1 Common Transformation Operations

6.2 Example of a Transformation Sequence

Suppose an imported sales table contains Sales Date, Customer, Region, Product, Quantity, and Sales Amount.

1. Promote the first row to headers if the source contains column names in the first row.
2. Rename columns to use clear and consistent names.
3. Change Sales Date to the Date data type.
4. Change Quantity to a suitable whole-number type.
5. Change Sales Amount to a decimal or fixed-decimal type, depending on the source and business requirements.
6. Remove rows that are genuinely invalid or not required.
7. Trim text columns if unnecessary spaces are present.
8. Check for duplicates according to the business key.
9. Validate the transformed data.

7. Changing Data Types

Every column should have a data type that matches the meaning of its values. Data types affect calculations, sorting, filtering, relationships, and how fields are used in visuals.

7.1 Common Data Types

7.2 Steps to Change a Data Type

1. Select the column in Power Query Editor.
2. Open the data type menu from the column header or Transform tab.
3. Choose the correct data type.
4. If prompted, select the appropriate replacement or conversion option.
5. Inspect the values for conversion errors.
6. Correct source-format issues if necessary.

7.3 Example

Suppose Sales Amount contains the values "1500", "2200", and "1750" stored as text. Changing the column to a numeric type allows Power BI to calculate sums and averages correctly.

However, a value such as "1,500.50" may require the correct locale or formatting settings. Confirm the decimal and thousands separators before converting it.

8. Filtering Rows and Removing Unwanted Data

Filtering and removing rows help retain the records required for analysis. The correct choice depends on whether the records are irrelevant, invalid, or simply outside the current reporting scope.

8.1 Filtering Rows

Filtering retains records that meet specified conditions. For example, a sales report may need only transactions from the current year or only records belonging to selected regions.

8.2 Steps to Filter Rows

1. Select the filter dropdown on the required column.
2. Choose the values to retain or use the available text, number, or date filters.
3. Set the required conditions.
4. Apply the filter.
5. Review the remaining records.

8.3 Removing Blank Rows

Blank rows can make a dataset appear larger than it is and may interfere with quality checks. Use the available Remove Rows options or filter out records that are genuinely empty.

Do not automatically remove every row containing a blank field. A missing value in an optional column may be valid, while a missing transaction identifier could indicate an invalid record.

8.4 Removing Duplicates

Removing duplicates can improve data quality, but first identify what makes a record unique. Two transactions can have identical dates and amounts while still representing separate legitimate sales.

Select the column or combination of columns that defines uniqueness, then use Remove Duplicates. Verify the row count and compare results with the source before proceeding.

9. Renaming, Removing, and Reordering Columns

9.1 Renaming Columns

Clear column names make queries easier to understand and help report creators select the correct fields.

Examples include changing Cust_Name to Customer Name, Txn_Date to Transaction Date, and Amt to Sales Amount.

9.2 Removing Columns

Remove columns that are unnecessary for the model, provided they are not required for later transformations, relationships, auditing, or business analysis.

9.3 Reordering Columns

Reordering columns changes their position in the query preview. It can improve readability, but column order usually does not determine the behavior of the data model or the layout of report visuals.

10. Splitting, Merging, and Extracting Column Values

Text transformations are frequently used when data arrives in a combined or inconsistent format.

10.1 Splitting Columns

Splitting divides one field into multiple fields or rows. For example, splitting "Mumbai, Maharashtra" at the comma creates separate City and State fields.

10.2 Merging Columns

Merging combines multiple fields into one. For example, First Name and Last Name can be merged with a space separator to create Full Name.

10.3 Extracting Text

Extraction retrieves a specific part of a value. For example, extracting the text after @ from an email address returns its domain.

10.4 Example of a Text Transformation

After applying text transformations, check for inconsistent delimiters, extra spaces, null values, and unexpected output.

11. Replacing Values and Handling Missing Data

Power Query provides options for replacing values and managing missing information. The objective is to standardize the data without hiding genuine data-quality problems.

11.1 Replacing Values

For example, a source system may use "M", "Male", and "male" to represent the same category. You can standardize these values when the business meaning is clear.

11.2 Handling Null Values

Null indicates that a value is missing or unavailable. It is not always equivalent to zero, an empty string, or the text "Unknown". Decide how missing values should be handled according to the field's meaning.

11.3 Example

12. Using Applied Steps to Manage Transformations

Applied Steps is a central feature of Power Query Editor. It displays the sequence of operations applied to a query and helps users understand how the current output was produced.

12.1 How Applied Steps Work

When you perform an operation, such as changing a data type or removing a column, Power Query records it as a step. Later steps are evaluated as part of the query transformation process.

12.2 Common Step Names

12.3 Editing or Removing a Step

1. Open Power Query Editor.
2. Choose the relevant query.
3. Locate the step in Applied Steps.
4. Select the step to inspect its result.
5. Use the available settings icon to edit the step when supported.
6. Use the delete control or context menu to remove a step when appropriate.
7. Review all subsequent steps for errors or unintended changes.

Removing a step can affect later steps that depend on a column or value created earlier. Review the query after making changes.

13. Understanding Power Query M

Power Query M is the formula language used to define data retrieval and transformation steps. Power Query generates M expressions when users apply operations through the interface.

13.1 Example of M Code

let
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(
        SalesSheet,
        [PromoteAllScalars=true]
    ),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"Sales Date", type date},
            {"Quantity", Int64.Type},
            {"Sales Amount", Currency.Type}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedTypes,
        each [Quantity] > 0
    )
in
    FilteredRows

This example connects to an Excel workbook, selects the Sales worksheet, promotes the first row to headers, changes selected data types, and retains records where Quantity is greater than zero. The file path and worksheet name must match the actual source.

13.2 Understanding the Code Structure

• let: Begins the expression and defines the transformation steps.
• Source: Represents the connection to the original data.
• Intermediate steps: Apply operations to the previous step's output.
• in: Specifies the final result returned by the query.

13.3 Benefits of M Code

• Supports repeatable transformations.
• Allows more advanced logic than some graphical operations.
• Makes complex query logic easier to inspect and document.
• Can help automate data preparation during refresh.
• Supports reusable patterns for common data-cleaning tasks.

14. Previewing and Validating Transformed Data

Before loading a query, validate that its output meets the intended requirements. A successful transformation does not automatically mean that the data is correct.

14.1 Validation Checklist

• Check that the expected columns are present.
• Verify column names and data types.
• Review representative records.
• Check for unexpected nulls and conversion errors.
• Confirm that filters retain the intended records.
• Compare row counts before and after major transformations.
• Verify that duplicate removal follows the correct business rule.
• Compare totals against a trusted source when appropriate.
• Check that relationships and keys will remain usable.

14.2 Example: Sales Validation

Suppose the source sales file contains 10,000 rows. After filtering out cancelled transactions, the query contains 9,400 rows. Verify that the 600 excluded rows correspond to cancelled transactions rather than valid sales accidentally removed by an incorrect filter.

If the source sales total is ₹5,00,000, compare the transformed total with the expected total for the same transaction scope. Differences should be explained before the report is published.

15. Loading Data Into Power BI

After applying the required transformations and validating the output, load the prepared data into Power BI Desktop.

15.1 What Happens During Loading?

Power BI evaluates the query and makes the resulting data available to the semantic model when the query is configured to load. Depending on the source, connector, and transformation steps, the operation may involve retrieving data, applying transformations, converting values, and storing the resulting table in the model.

15.2 Steps to Load Data

1. Open Power Query Editor.
2. Select each query that should be included in the model.
3. Confirm that the required transformations have been applied.
4. Review errors and validate the preview.
5. Open the Home tab.
6. Select Close & Apply.
7. Wait for Power BI Desktop to process the queries.
8. Review the resulting tables in the Data or Model view.
9. Confirm that the expected columns and records are available.

15.3 What Is Close & Apply?

Close & Apply closes Power Query Editor and applies pending query changes to the Power BI Desktop model. It is the usual command for applying transformations and loading configured queries.

The time required depends on the data volume, number of queries, source performance, transformation complexity, and available system resources.

16. Understanding Enable Load

Not every query needs to become a separate table in the semantic model. Some queries are created only to prepare or support other queries.

16.1 What Is Enable Load?

Enable Load determines whether a query's result is loaded as a table into the model. When loading is disabled, the query can still be used as an intermediate query by other queries, subject to the overall query design.

16.2 Example

Suppose a project uses three queries:

16.3 How to Disable or Enable Loading

1. Open Power Query Editor.
2. Locate the query in the Queries pane.
3. Right-click the query.
4. Select or clear Enable Load, depending on the current setting.
5. Apply the changes using Close & Apply.

Menu wording and behavior can vary slightly by Power BI version. Before disabling a query's load setting, confirm that it is not required as a visible table in the semantic model.

17. Understanding Load Settings and Query Dependencies

Queries may depend on other queries. For example, a final sales query may reference a staging query that imports and cleans the source file.

17.1 Why Use Staging Queries?

• Separate raw data preparation from final model tables.
• Reuse common cleaning logic.
• Improve organization in projects with multiple related queries.
• Make query dependencies easier to understand.
• Keep intermediate preparation tables out of the visible model when appropriate.

17.2 Example Workflow

1. Create a query that connects to the source file.
2. Apply common cleaning operations in the staging query.
3. Create a reference query for the final reporting table.
4. Apply additional transformations required by the reporting table.
5. Enable loading for the final table if required.
6. Disable loading for intermediate queries that do not need to appear in the model.
7. Apply changes and validate the model.

A reference query uses the output of another query as its starting point. It can help organize transformations, but it does not automatically guarantee faster refreshes. Performance depends on the data source, connector, folding behavior, and query design.

18. Understanding Import and DirectQuery Storage Modes

Power BI supports different ways of working with data. Import and DirectQuery are two important storage modes, and they affect how data is accessed and refreshed.

18.1 Import Mode

In Import mode, data is loaded into the Power BI semantic model. Reports typically use this stored data, and source changes become available after the relevant refresh completes.

18.2 DirectQuery Mode

In DirectQuery mode, data generally remains in the underlying source. Power BI sends queries to the source for supported report operations. Performance depends on source capacity, query complexity, network conditions, and connector behavior.

18.3 Choosing a Storage Mode

Choose a storage mode based on data volume, source performance, freshness requirements, modeling requirements, security, and supported transformations. Not every connector or transformation supports every storage mode in the same way.

19. Understanding Query Folding

Query folding occurs when Power Query translates supported transformations into operations that can be executed by the source system. For example, a filter applied to a SQL Server table may be translated into a source-side query rather than being performed entirely after retrieving all rows.

19.1 Benefits of Query Folding

• Can reduce the amount of data transferred from a source.
• Can allow the source system to perform supported transformations.
• Can improve refresh performance for suitable workloads.
• Can help Power Query work efficiently with large relational datasets.

19.2 Examples of Potentially Foldable Operations

Depending on the connector and source capabilities, operations such as filtering rows, selecting columns, and changing data types may be folded. Some complex text operations, custom functions, or operations involving unsupported logic may prevent folding beyond a particular step.

19.3 How to Check Query Folding

1. Open Power Query Editor.
2. Select a query connected to a supported source.
3. Right-click an applicable step in Applied Steps.
4. Look for View Native Query if the connector and step support it.
5. Use available query diagnostics or source-side monitoring when necessary.

View Native Query is not available for every connector or every step. Its absence does not, by itself, prove that no folding is occurring.

20. Refreshing Data After Loading

Loading applies the current query results to the model. Refreshing runs the configured data retrieval and transformation process again to update the model from the source.

20.1 Refresh in Power BI Desktop

1. Open the Power BI Desktop report.
2. Confirm that the source is accessible and credentials are valid.
3. Select Refresh from the Home ribbon.
4. Wait for queries and model updates to complete.
5. Review any error messages.
6. Check important tables, totals, and visuals.

20.2 What Happens During Refresh?

• Power BI evaluates the configured queries.
• Data is retrieved from the applicable source or sources.
• Power Query transformations are applied.
• Configured tables are updated according to the storage mode and model settings.
• Reports use the refreshed model data where applicable.

20.3 Refresh Considerations

• Check that file paths and database connections remain valid.
• Confirm credentials and permissions.
• Review schema changes in the source.
• Monitor refresh duration and query errors.
• Validate critical business totals after refresh.
• Configure scheduled refresh in the Power BI service when required and supported by the source and licensing setup.

21. Managing Errors During Transformation and Loading

Errors can occur when source data changes, values cannot be converted to the selected type, columns are missing, credentials expire, or a transformation expects a format that the source no longer follows.

21.1 Common Errors and Solutions

21.2 A Structured Troubleshooting Process

1. Identify the query and step associated with the error.
2. Inspect the data preview immediately before the failing step.
3. Check whether the source schema or sample values have changed.
4. Review the relevant transformation or M expression.
5. Correct the source data or adjust the query logic.
6. Reapply the transformation and validate the result.
7. Run a refresh and verify the final model output.

22. Improving Transformation and Loading Performance

Efficient data preparation reduces unnecessary work and can improve refresh times. The best approach depends on the data source, the size of the dataset, and the complexity of the query.

22.1 Recommended Practices

• Remove columns that are not needed for reporting or subsequent transformations.
• Filter out records that are genuinely outside the required scope.
• Prefer straightforward transformations where possible.
• Preserve query folding for supported sources when practical.
• Avoid repeatedly performing the same expensive transformation.
• Use suitable data types rather than leaving every field as text.
• Reduce unnecessary high-cardinality text fields when they are not required in the model.
• Use staging and reference queries when they improve organization and reuse.
• Investigate slow source queries separately from Power Query processing time.
• Validate results after every major optimization.

22.2 Example

Suppose a SQL Server table contains 2 million rows and 45 columns, but a report only requires 6 columns and transactions from the last two years. Selecting the required columns and applying an appropriate date filter early may reduce the amount of data processed or transferred, particularly when those operations fold to the source.

Do not remove fields that are needed for relationships, calculations, auditing, or future transformations simply to reduce the data size.

23. Complete Practical Project: Sales Data Preparation and Loading

In this project, you will import sales data, clean the dataset, apply transformations, load the result into Power BI, and validate the output.

23.1 Project Scenario

A retail company receives a monthly Excel file containing transaction details. The dataset contains transaction dates, customer names, regions, products, quantities, sales amounts, and order statuses. The company wants to create a reliable Power BI sales report.

23.2 Sample Source Data

23.3 Step 1: Connect to the Source

1. Open Power BI Desktop.
2. Select Get Data and choose Excel workbook.
3. Choose the monthly sales file.
4. Select the appropriate table or worksheet in Navigator.
5. Choose Transform Data.

23.4 Step 2: Inspect the Data

• Confirm that the expected columns are present.
• Check whether headers are correctly identified.
• Inspect the first several records.
• Identify blank values, inconsistent labels, and possible duplicates.

23.5 Step 3: Apply Transformations

1. Promote headers if the first row contains the column names.
2. Rename fields to clear business-friendly names.
3. Set Order Date to Date.
4. Set Quantity to Whole Number.
5. Set Sales Amount to an appropriate numeric type.
6. Trim Region, Product, and Customer fields if unnecessary spaces exist.
7. Review Order ID for duplicates according to the transaction rules.
8. Filter out cancelled orders if the report specifically measures completed sales only.
9. Retain fields required for reporting, relationships, and validation.

23.6 Step 4: Validate the Results

Confirm that only the intended records remain. In this example, three transactions are marked Completed and one is marked Cancelled. If the reporting rule excludes cancelled orders, the final query should contain the three completed transactions shown in the sample.

Verify the completed sales total:

120000 + 2500 + 18000 = 140500

The expected total for the three completed sample orders is 140,500. Use the same inclusion rules when comparing this figure with the Power BI result.

23.7 Step 5: Load the Data

1. Review the Applied Steps.
2. Confirm that the query has no unintended errors.
3. Select Close & Apply.
4. Wait for Power BI to apply the transformations.
5. Open Data view or Model view.
6. Confirm that the sales table is available and that the data types are correct.

23.8 Step 6: Create a Simple Report

• Create a card visual showing completed sales.
• Create a bar chart showing sales by region.
• Create a column chart showing sales by product.
• Add a date slicer based on Order Date.
• Add a region slicer if users need to filter the report.

23.9 Step 7: Refresh and Validate Again

Update the source file with a new valid transaction and refresh the model. Confirm that the new transaction is included and that the transformations still work. Also test a malformed date or unexpected status in a safe copy of the source to understand how the query handles source changes.

24. Practical Exercises

Exercise 1: Transform an Excel Dataset

Import an Excel file containing customer records.

• Promote headers if needed.
• Rename the columns.
• Assign appropriate data types.
• Remove genuinely unnecessary columns.
• Filter records according to a defined requirement.
• Validate the results before loading.

Exercise 2: Clean a Sales Dataset

Prepare a sales table containing order date, customer, region, quantity, sales amount, and status.

• Identify blank values and potential duplicates.
• Correct data types.
• Standardize inconsistent region labels.
• Apply a filter for completed orders.
• Compare the final total with a trusted source total.

Exercise 3: Use Applied Steps

Create a query with at least five transformations.

• Inspect each step.
• Rename an automatically generated step where appropriate.
• Edit one transformation.
• Remove one step and observe the impact on later steps.
• Restore the required transformation and validate the query.

Exercise 4: Manage Query Loading

Create a staging query and a final reporting query.

• Use the staging query to prepare the source data.
• Create a reference query for the final table.
• Configure Enable Load according to each query's purpose.
• Apply the changes.
• Check which tables appear in the model.

Exercise 5: Test Refresh

Load a file-based dataset and then modify the source file.

• Add a new valid record.
• Refresh Power BI Desktop.
• Confirm that the new record is included.
• Test a source change that could cause an error, such as an unexpected text value in a numeric column.
• Inspect and correct any errors.

25. Interview Questions and Answers

Q1. What is Power Query Editor?

Answer: Power Query Editor is the environment in Power BI used to connect to data sources and transform data before loading it into the semantic model.

Q2. What is data transformation?

Answer: Data transformation is the process of changing data values, types, structure, or organization to make the data suitable for analysis.

Q3. What is the purpose of Close & Apply?

Answer: Close & Apply closes Power Query Editor and applies pending query changes to Power BI Desktop, loading the results of queries configured for loading.

Q4. What are Applied Steps?

Answer: Applied Steps records the transformation operations performed on a query and allows users to inspect, edit, or remove steps where supported.

Q5. What is Power Query M?

Answer: Power Query M is the formula language used to define data retrieval and transformation logic in Power Query.

Q6. Why should data types be assigned correctly?

Answer: Correct data types support accurate calculations, sorting, filtering, relationships, and the appropriate use of fields in visuals.

Q7. What is Enable Load?

Answer: Enable Load controls whether a query's output is loaded as a table into the semantic model. Intermediate queries may be kept as preparation queries when they do not need to appear as model tables.

Q8. What is a staging query?

Answer: A staging query is an intermediate query used to import and prepare source data before other queries use it to create final reporting tables.

Q9. What is the difference between Import and DirectQuery?

Answer: Import stores data in the Power BI semantic model, while DirectQuery generally keeps data in the source and sends queries to it for supported operations. Each mode has different performance, freshness, and transformation considerations.

Q10. What is query folding?

Answer: Query folding is the process of translating supported Power Query transformations into operations that can run at the data source.

Q11. Why should data be validated before loading?

Answer: Validation helps identify incorrect data types, missing values, unexpected row counts, duplicates, and transformation errors before the data is used in reports.

Q12. What causes data type conversion errors?

Answer: They occur when one or more values cannot be converted to the selected type, often because of inconsistent formatting, invalid values, or locale differences.

Q13. Does loading data mean the source is permanently updated?

Answer: No. Loading transformed data into Power BI does not ordinarily change the original source file or database. Power Query prepares the data for the Power BI model.

Q14. What is the difference between Close & Apply and Refresh?

Answer: Close & Apply applies pending query edits and loads the resulting data. Refresh reruns the configured data retrieval and transformation process to update the model with source data.

Q15. How can transformation performance be improved?

Answer: Remove unnecessary data, use appropriate filters, avoid redundant operations, maintain query folding when practical, and investigate source and model performance.

26. Best Practices for Applying Transformations and Loading Data

• Understand the source data before changing it.
• Keep transformation steps clear and logically organized.
• Assign the correct data type to every important field.
• Remove only columns and records that are genuinely unnecessary.
• Define a business rule before removing duplicates.
• Handle missing and invalid values consistently.
• Use meaningful query and column names.
• Review Applied Steps after making changes.
• Validate totals and row counts before loading.
• Use Enable Load appropriately for staging and reporting queries.
• Preserve query folding where practical for supported sources.
• Test refreshes after changes to source files or database schemas.
• Keep a backup of important source data and maintain version control for complex query logic where possible.
• Document important business rules, such as excluding cancelled transactions.

27. Quick Revision Checklist

• Understand the Power BI data preparation workflow.
• Know how to connect to a data source.
• Open Power Query Editor using Transform Data.
• Apply common transformations to rows and columns.
• Change data types and handle conversion errors.
• Filter rows and remove duplicates according to business rules.
• Use Applied Steps to inspect the transformation sequence.
• Understand the role of Power Query M.
• Validate transformed data before loading.
• Use Close & Apply to apply query changes.
• Understand Enable Load and staging queries.
• Distinguish Import mode from DirectQuery mode.
• Understand query folding and its potential performance benefits.
• Refresh the model and verify the results.
• Troubleshoot source, transformation, and loading errors.

28. Conclusion

Applying transformations and loading data into Power BI are essential steps in building reliable reports and dashboards. Power Query Editor provides tools to clean, reshape, filter, and standardize data before it becomes available for modeling and analysis.

A successful workflow involves connecting to the source, inspecting data quality, applying appropriate transformations, reviewing Applied Steps, validating the output, and using Close & Apply to load the configured queries into Power BI Desktop.

Understanding Enable Load, query dependencies, storage modes, query folding, and refresh behavior helps users build data workflows that are easier to maintain and troubleshoot. Always validate important records and business totals before using transformed data for decision-making.

To continue learning, explore the Microsoft Power BI Training course, register for the JustAcademy course demo, or visit the JustAcademy website.

29. Additional Learning Resources

• Microsoft Learn: Power Query Documentation
• Microsoft Learn: Power Query User Interface
• Microsoft Learn: Applied Steps
• Microsoft Learn: Query Folding Basics
• Microsoft Learn: Refresh Data in Power BI
• Microsoft Learn: Storage Modes in Power BI Desktop